<?php
/**
 * Created by PhpStorm.
 * User: ouryun
 * Date: 2021/8/6
 * Time: 14:06
 */

namespace app\admin\controller;


use think\admin\Controller;
use think\facade\Db;

/**
 * 客流数据分析
 * Class Analyse
 * @package app\admin\controller
 */
class Analyse extends Controller
{
    /**
     * 人脸识别数据表
     * @var string
     */
    public $table = 'lg_face_recognition';

    /**
     * 小时
     * @var array
     */
    public $hours = [
        '1' => 0, '2' => 0, '3' => 0, '4' => 0, '5' => 0, '6' => 0,
        '7' => 0, '8' => 0, '9' => 0, '10' => 0, '11' => 0, '12' => 0,
        '13' => 0, '14' => 0, '15' => 0, '16' => 0, '17' => 0, '18' => 0,
        '19' => 0, '20' => 0, '21' => 0, '22' => 0, '23' => 0, '24' => 0
    ];

    /**
     * 客流数据分析
     * @login true
     * @auth true
     */
    public function index()
    {
        $this->title = '客流数据分析';
        $shoList = $this->app->db->name('lg_distributor')->field('id,name')->select()->toArray();
        $this->fetch('', [
            'shopList' => $shoList
        ]);
    }


    /**
     * @return \think\response\Json
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function getPassengerFlow()
    {
        $data = $this->request->param();
        $vali = $this->app->validate->check($data, [
            'stage|时间段' => 'require',
            'angle|视角' => 'require'
        ]);
        if ($vali !== true) {
            return json(['code' => 400, 'msg' => $this->app->validate->getError(), 'data' => '']);
        }
        $stage = $data['stage'];
        $angle = $data['angle'];
        $shopId = $data['shop_id'] ?? 0;
        $whereMap = [];
        if ($shopId != 0) {
            $whereMap['shop_id'] = $shopId;
        }
        $now = $prev = $title = [];
        $day = strtotime(date('Y-m-d', time()));
        $date = date('Y-m-d', time());
        switch ($stage) {
            case 'day':
                $hours = $this->hours;
                $title[0] = '今天';
                //今天
                $nowList = $this->app->db->name($this->table)
                    ->whereDay('enter_time')
                    ->field('count(id) AS total,FROM_UNIXTIME(enter_time, "%H") AS section')
                    ->where($whereMap)
                    ->group('section')
                    ->select()->toArray();
                if ($angle == 'hb') {
                    $title[1] = '昨天';
                    //昨天
                    $prevList = $this->app->db->name($this->table)
                        ->whereDay('enter_time', 'yesterday')
                        ->field('count(id) AS total,FROM_UNIXTIME(enter_time, "%H") AS section')
                        ->where($whereMap)
                        ->group('section')
                        ->select()->toArray();
                } else {
                    //上个月今天
                    $title[1] = '上个月今天';
                    $startTime = strtotime('-1 month', $day);
                    $endTime = strtotime('-1 day', $startTime);
                    $prevList = $this->app->db->name($this->table)
                        ->where('enter_time', '>=', $endTime)
                        ->where('enter_time', '<', $startTime)
                        ->where($whereMap)
                        ->field('count(id) AS total,FROM_UNIXTIME(enter_time, "%H") AS section')
                        ->group('section')
                        ->select()->toArray();
                }
                foreach ($hours as $key => $item) {
                    $now[$key] = 0;
                    $prev[$key] = 0;
                    foreach ($nowList as $k => $v) {
                        if ($key == $v['section']) {
                            $now[$key] = $v['total'] ?? 0;
                        }
                    }
                    foreach ($prevList as $kk => $vo) {
                        if ($key == $vo['section']) {
                            $prev[$key] = $vo['total'] ?? 0;
                        }
                    }
                }
                $abscissa = array_keys($now);
                $nowLine = array_values($now);
                $prevLine = array_values($prev);
                break;
            case 'week':
                $title[0] = '近七天';
                //近七天
                $startTime = strtotime('-6 day', $day);
                $now = $this->queryData($startTime, time(), $date, 6, $whereMap);
                if ($angle == 'hb') {
                    $title[1] = '上月同七天';
                    //上月七天
                    $start = strtotime('-1 month', $day);
                    $end = strtotime('-6 day', $start);
                    $prevList = $this->queryData($end, ($start + 86399), date('Y-m-d', $start), 6, $whereMap);
                } else {
                    //去年这七天
                    $title[1] = '去年这七天';
                    $start = strtotime('-1 year', $day);
                    $end = strtotime('-6 day', $start);
                    $prevList = $this->queryData($end, ($start + 86399), date('Y-m-d', $start), 6, $whereMap);
                }
                $abscissa = array_column($now, 'num');
                $nowLine = array_column($now, 'total');
                $prevLine = array_column($prevList, 'total');
                break;
            case 'month':
                $title[0] = '近30天';
                //近30天
                $startTime = strtotime('-30 day', $day);
                $nowList = $this->queryData($startTime, time(), $date, 29, $whereMap);
                if ($angle == 'hb') {
                    //上年同30天
                    $title[1] = '上年同30天';
                    $start = strtotime('-1 year', $day);
                    $end = strtotime('-30 day', $start);
                    $prevList = $this->queryData($end, ($start + 86399), date('Y-m-d', $start), 29, $whereMap);
                } else {
                    $title[1] = '';
//                    $start = strtotime('-1 year', $day);
//                    $end = strtotime('-30 day', $start);
//                    $prevList = $this->queryData($end, ($start + 86399), date('Y-m-d', $start), 29, $whereMap);
                    $prevList = [];
                }
                $abscissa = array_column($nowList, 'num');
                $nowLine = array_column($nowList, 'total');
                $prevLine = array_column($prevList, 'total');
                break;
            default:
                $abscissa = [];
                $nowLine = [];
                $prevLine = [];
                break;
        }
        return json([
            'code' => '200',
            'msg' => '请求成功',
            'data' => [
                'title' => $title,
                'abscissa' => $abscissa,   //横坐标
                'now_line' => $nowLine,    //现在的线
                'prev_line' => $prevLine,   //以前的线
            ]
        ]);
    }

    /**
     * @param $startTime
     * @param $endTime
     * @param $date
     * @param $num
     * @param $whereMap
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function queryData($startTime, $endTime, $date, $num, $whereMap)
    {
        $nowList = $this->app->db->name($this->table)
            ->whereBetween('enter_time', $startTime . ',' . $endTime)
            ->field('count(id) AS total,FROM_UNIXTIME(enter_time, "%Y-%m-%d") AS section')
            ->where($whereMap)
            ->group('section')
            ->select()->toArray();
        $startDate = date('Y-m-d', strtotime("-{$num} day", strtotime($date)));
        $dateTime = [];
        for ($i = 0; $i <= $num; $i++) {
            if ($i == 0) {
                $dateTime[] = $startDate;
            } else {
                $dateTime[] = date('Y-m-d', strtotime("+{$i} day", strtotime($startDate)));
            }
        }
        $now = [];
        foreach ($dateTime as $key => $vo) {
            $now[$key]['total'] = 0;
            $now[$key]['section'] = $vo;
            $now[$key]['num'] = $key + 1;
            foreach ($nowList as $k => $v) {
                if ($vo == $v['section']) {
                    $now[$key]['total'] = $v['total'];
                }
            }
        }
        return $now;
    }

    /**
     * 查询sql
     * @param $num
     * @param $startTime
     * @param $endTime
     * @param $map
     * @return mixed
     */
    protected function _querySql($num, $startTime, $endTime, $map)
    {
        if (empty($map)) {
            $where = '';
        } else {
            $where = "AND shop_id = {$map}";
        }
        $sql = <<<SQL
SELECT
    tbl.`_date` AS `_date`,
    IFNULL( tbr.total, 0 ) AS total,tbr.section,tbr.id
FROM
    (
SELECT
    @s := @s + 1 AS _index,
    DATE( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ) AS `_date`
FROM
    information_schema.COLLATION_CHARACTER_SET_APPLICABILITY,
    ( SELECT @s := - 1 ) temp
WHERE
    @s < {$num}
ORDER BY
    `_date`  ASC
    ) AS tbl
LEFT JOIN ( SELECT id,count( id ) total, FROM_UNIXTIME( enter_time, "%Y-%m-%d" ) AS `section`  FROM lg_face_recognition WHERE  `enter_time` BETWEEN {$startTime} AND {$endTime} {$where}  GROUP BY section  ) AS tbr ON tbl.`_date` = tbr.section ORDER BY _date
SQL;
        $list = Db::query($sql);
        $arr = [];
        foreach ($list as $key => $value) {
            $arr[$key]['section'] = $key + 1;
            $arr[$key]['data'] = $value['_date'];
            $arr[$key]['total'] = $value['total'];
        }
        return $arr;
    }

}